/**
 * �����DAO�࣬������ֻ��ԭ��ʵ�� getDBConnection ����(���ṩ��ݿ�l��)����
 */
package cn.antia.db;

import cn.antia.cep.common.AntiaLog;
import cn.antia.cwin.db.DBConfig;

import java.util.*;
import java.sql.*;
import java.lang.reflect.Method;

/**
 * User: Jacob Xiao
 * Date: 2008-6-27
 * Time: 16:39:48
 */
public abstract class AbstractDAO implements GenericDAO{
    private  SQLGene sqlGene;

    protected AbstractDAO() {
        //sqlGene = DBConfig.getSqlGene();
    }

    public SQLGene getSqlGene() {
        return sqlGene;
    }
    public void setSqlGene(SQLGene sqlGene) {
        this.sqlGene = sqlGene;
    }

    public <T> List<T> queryList(String sql,int page,int pageSize,String orderField,boolean isAsc,Class<T> clazz){
		sql = sqlGene.composeSqlPage(sql, orderField, isAsc, page, pageSize);
		return queryList(sql,clazz);
	}

    public <T> List<T> queryList(JdbcSql sql,int page,int pageSize,String orderField,boolean isAsc,Class<T> clazz){
        sql.setSql(sqlGene.composeSqlPage(sql.getSql(), orderField, isAsc, page, pageSize));
        return queryList(sql,clazz);
	}
    
    public  <T> List<T> queryList(String sql,Class<T> clazz) {
    	List<T> relist = new ArrayList<T>();
    	List<Map<String,Object>> list = queryListMap(sql);
    	if(list.size() < 1){
    		return relist;
    	}
        try{
            for(Map<String,Object> data:list){
                relist.add(mapToClass(data,clazz));
            }
        }catch(Exception e){
            AntiaLog.error("SQL��ѯ��̬��������ʧ�ܡ�");
        }
        return relist;
    }

    public  <T> List<T> queryList(JdbcSql sql,Class<T> clazz) {
    	List<T> relist = new ArrayList<T>();
    	List<Map<String,Object>> list = queryListMap(sql);
    	if(list.size() < 1){
    		return relist;
    	}
        try{
            for(Map<String,Object> data:list){
                relist.add(mapToClass(data,clazz));
            }
        }catch(Exception e){
            AntiaLog.error("SQL��ѯ��̬��������ʧ�ܡ�");
        }
        return relist;
    }

    /**
     * ȡ������SQL�ļ�¼����
     *
     * @param sql sql
     * @return record count
     * */
    public  int getRecordCount(String sql){
    	sql = sqlGene.getRecordCountSQL(sql);
        Integer i = queryFirstObject(sql);
        return i;
    }

    public  int getRecordCount(JdbcSql sql){
    	sql.setSql(sqlGene.getRecordCountSQL(sql.getSql()));
        Integer i = queryFirstObject(sql);
        return i;
    }

    /**
     * ���ص�һ����ڶ��е�MAP���Ե�һ��ΪKEY���ڶ���ΪVALUE
     * @param sql sql,��ѯ�ֶα���Ϊ2������ϣ����򽫷���Ϊ�յ�MAP
     * @return Map<K, T>
     */
    public  <K,T> Map<K, T> queryMap(String sql) {
        Map<K, T> map = new HashMap<K,T>();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            if(metaData.getColumnCount() < 2){
                return map;
            }
            //��ResultSet�е�ֵ����LIST��
            while (rs.next()) {
                map.put((K)rs.getObject(1),(T)rs.getObject(2));
            }
            // ����
        } catch (SQLException e) {
            AntiaLog.error(e.toString());
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        return map;
    }

    public  <K,T> Map<K, T> queryMap(JdbcSql sql) {
        Map<K, T> map = new HashMap<K,T>();
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = getPreparedStatement(conn, statement, sql);
            rs = statement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            if(metaData.getColumnCount() < 2){
                return map;
            }
            //��ResultSet�е�ֵ����LIST��
            while (rs.next()) {
                map.put((K)rs.getObject(1),(T)rs.getObject(2));
            }
            // ����
        } catch (SQLException e) {
            AntiaLog.error(e.toString());
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        return map;
    }

    /**
     * ִ�в�ѯ���н���SQL
     * @param sql       sql
     * @return          list<T>
     * added by xiaobin 2008-4-1
     */
    public  <T> List<T> queryFirstField(String sql){
        List<T> ts = new ArrayList<T>();
        Connection conn = null;
        Statement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()){
                ts.add((T)rs.getObject(1));
            }
        } catch (SQLException e) {
            AntiaLog.error(e.toString());
        }finally {
           DBUtils.closeQuietly(conn,stmt,rs);
        }
        return ts;
    }
    public  <T> List<T> queryFirstField(JdbcSql sql){
        List<T> ts = new ArrayList<T>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            stmt = getPreparedStatement(conn, stmt, sql);
            rs = stmt.executeQuery();
            while (rs.next()){
                ts.add((T)rs.getObject(1));
            }
        } catch (SQLException e) {
            AntiaLog.error(e.toString());
        }finally {
            DBUtils.closeQuietly(conn,stmt,rs);
        }
        return ts;
    }

    public List<Map<String, Object>> queryListMap(String sql, int page, int pageSize, String orderField, boolean isAsc) {
        sql = sqlGene.composeSqlPage(sql, orderField, isAsc, page, pageSize);
        return queryListMap(sql);
    }
    public List<Map<String, Object>> queryListMap(JdbcSql sql, int page, int pageSize, String orderField, boolean isAsc) {
        sql.setSql(sqlGene.composeSqlPage(sql.getSql(), orderField, isAsc, page, pageSize));
        return queryListMap(sql);
    }

    /**
     * ���SQL,���ز�ѯ���,List<Map<field,value>>
     *
     * @param sql:SQL���
     * @return list:(����Ϣ�б?һ����Ϣ��һ��Map���)
     * added by xiaobin 2008-4-1
     */
    public  List<Map<String,Object>> queryListMap(String sql) {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Set<String> fields = new HashSet<String>();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            //�����ж���
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                fields.add(metaData.getColumnName(i).trim().length() < 1 ? metaData.getColumnLabel(i) : metaData.getColumnName(i));
            }
            //��ResultSet�е�ֵ����LIST��
            while (rs.next()) {
                Map<String,Object> record = new HashMap<String,Object>();
                for (String field : fields) {
                    record.put(field, rs.getObject(field));
                }
                list.add(record);
            }
            // ����
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        // ����
        return list;
    }

    /**
     * ���SQL,���ز�ѯ���,List<Map<field,value>>
     *
     * @param sql:SQL���
     * @return list:(����Ϣ�б?һ����Ϣ��һ��Map���)
     * added by xiaobin 2008-4-1
     */
    public  List<Map<String,Object>> queryListMap(JdbcSql sql) {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        Set<String> fields = new HashSet<String>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            pstmt = getPreparedStatement(conn, pstmt, sql);
            rs = pstmt.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            //�����ж���
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                fields.add(metaData.getColumnName(i).trim().length() < 1 ? metaData.getColumnLabel(i) : metaData.getColumnName(i));
            }
            //��ResultSet�е�ֵ����LIST��
            while (rs.next()) {
                Map<String,Object> record = new HashMap<String,Object>();
                for (String field : fields) {
                    record.put(field, rs.getObject(field));
                }
                list.add(record);
            }
            // ����
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeQuietly(conn,pstmt,rs);
        }
        // ����
        return list;
    }

    /**
     * ��ѯ��һ��ֵ
     * @param sql sql
     * @return T
     */
    public <T> T queryFirstObject(String sql) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            if(rs.next()) {
                return (T) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }
    public <T> T queryFirstObject(JdbcSql sql) {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = getPreparedStatement(conn, statement, sql);
            rs = statement.executeQuery();
            if(rs.next()) {
                return (T) rs.getObject(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        return null;
    }

    private PreparedStatement getPreparedStatement(Connection conn,PreparedStatement ps, JdbcSql sql)throws SQLException {
        ps = conn.prepareStatement(sql.getSql());
        int j = 0;
        for (Object o : sql.getValues()) {
            ps.setObject(j++, o);
        }
        return ps;
    }

    /**
     * ���SQL,���ص�һ�в�ѯ���,Map<field,value>
     *
     * @param sql:SQL���
     * @return Map<field,value>:(����Ϣ�б�)
     */
    public Map<String,Object> queryFirstMap(String sql){
        Map<String,Object> record = new HashMap<String,Object>();
        Set<String> fields = new HashSet<String>();
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = conn.createStatement();
            rs = statement.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();
            //�����ж���
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                fields.add(metaData.getColumnName(i).trim().length() < 1 ? metaData.getColumnLabel(i) : metaData.getColumnName(i));
            }
            //��ResultSet�е�ֵ����LIST��
            if (rs.next()) {
                for (String field : fields) {
                    record.put(field, rs.getObject(field));
                }
            }
            // ����
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        // ����
        return record;
    }

    /**
     * ���SQL,���ص�һ�в�ѯ���,Map<field,value>
     *
     * @param sql:SQL���
     * @return Map<field,value>:(����Ϣ�б�)
     */
    public Map<String,Object> queryFirstMap(JdbcSql sql){
        Map<String,Object> record = new HashMap<String,Object>();
        Set<String> fields = new HashSet<String>();
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = getPreparedStatement(conn, statement, sql);
            rs = statement.executeQuery();
            ResultSetMetaData metaData = rs.getMetaData();
            //�����ж���
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                fields.add(metaData.getColumnName(i).trim().length() < 1 ? metaData.getColumnLabel(i) : metaData.getColumnName(i));
            }
            //��ResultSet�е�ֵ����LIST��
            if (rs.next()) {
                for (String field : fields) {
                    record.put(field, rs.getObject(field));
                }
            }
            // ����
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeQuietly(conn,statement,rs);
        }
        // ����
        return record;
    }

    /**
     * ��ѯ��һ�м�¼����JavaBean���󷵻�
     * @param sql sql
     * @param clazz T
     * @return T
     * @throws SQLException e
     */
    public <T> T queryObject(String sql,Class<T> clazz)throws SQLException{
        Map<String, Object> data = queryFirstObject(sql);
    	if(data == null){
    		return null;
    	}
        return mapToClass(data, clazz);
    }
    public <T> T queryObject(JdbcSql sql,Class<T> clazz)throws SQLException{
        Map<String, Object> data = queryFirstObject(sql);
    	if(data == null){
    		return null;
    	}
        return mapToClass(data, clazz);
    }

    /**
     * ��map��keyֵΪ�����������setter�������Զ�����һ����󷵻�
     * @param data Map<String,Object>
     * @param clazz T
     * @return T t
     */
    public <T> T mapToClass(Map<String,Object> data,Class<T> clazz){
        T t;
        String setMethod;
        try{
            t = clazz.newInstance();
        }catch(Exception e){
            e.printStackTrace();
            return null;
        }
        //��ķ����б�
        Method[] ms = t.getClass().getMethods();
        //ѭ������ֵ
        Object value;
        for(String key :data.keySet()){
            value = data.get(key);
            if(value == null){
                continue;
            }
            //set �ķ�����
            setMethod = "set" + key.substring(0,1).toUpperCase() + key.substring(1);
            //��ķ����б�
            for(Method m : ms){
                if(m.getName().equals(setMethod)){
                    //��������
                    Class[] cs = m.getParameterTypes();
                    if(cs == null || cs.length == 0){
                        continue;
                    }
                    try{
                        m.invoke(t, value);
                    }catch(Exception e){
                        break;
                    }
                    break;
                }
            }
        }
        return t;
    }

    public JdbcSql whereStatement(Map<String, Object> conditions, boolean isExact) {
       if (conditions == null) {
            return null;
        }
        StringBuilder where = new StringBuilder();
        Iterator<String> it = conditions.keySet().iterator();
        String field;
        Object value;
        int i = 0;
        Object[] values = new Object[conditions.size()];
        while (it.hasNext()) {
            field = it.next();
            value = conditions.get(field);
            if(value == null){
            	continue;
            }
            if (isExact) {
                where.append("and ").append(field).append(" like ? ");
            } else {
                where.append("and ").append(field).append(" = ? ");
            }
            values[i++] = value;
        }
        return new JdbcSql(where.length() > 3 ? where.substring(3) : "",values);
    }

    /**
     * @param sql:SQL语句
     * @return 返回LIST，所存对象是HASHMAP
     */
    public int update(String sql) {
        Connection conn = null;
        Statement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = conn.createStatement();
            return statement.executeUpdate(sql);
        } catch (Exception e) {
           e.printStackTrace();
            return 0;
        }finally {
           DBUtils.closeQuietly(conn,statement,rs);
        }
    }
     /**
     * @param sql:SQL语句
     * @return 返回LIST，所存对象是HASHMAP
     */
    public int update(JdbcSql sql) {
        Connection conn = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        try {
            conn = getDBConnection();
            statement = getPreparedStatement(conn, statement, sql);
            return statement.executeUpdate();
        } catch (Exception e) {
           e.printStackTrace();
           return 0;
        }finally {
           DBUtils.closeQuietly(conn,statement,rs);
        }
    }
}
